package live.jipiao.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import live.common.DBHelper;
import live.common.Format;
import live.common.Page;
import live.cz.entity.Order;
import live.jipiao.entity.AirOrder;


public class QueryOrders {
	private Format format = Format.getInstance();
	private StringBuffer sql = null;
	/**
	 * 获取用户机票订单信息
	 * @param memberNo
	 * @return
	 * @throws SQLException
	 */
	public List<AirOrder> list_Orders(String memberNo,AirOrder order,Page page) throws SQLException {
		DBHelper dh = DBHelper.getInstance();
		String execSql = "{call pro_Common_PageView(?,?,?,?)}";
		sql = new StringBuffer("SELECT * FROM Plane_Order WHERE 1=1 AND memberNum='").append(memberNo).append("' ");
		if(format.isNotNullOrEmpty(order.getStartDate())){
			sql.append(" AND bookDate>'").append(order.getStartDate()).append("' ");
		}
		if(format.isNotNullOrEmpty(order.getEndDate())){
			sql.append(" AND bookDate<'").append(format.getInstance().returnNextDate(order.getEndDate().toString())).append("' ");
		}
		if(format.isNotNullOrEmpty(order.getOrderStatus())){
			sql.append(" AND orderStatus=").append(order.getOrderStatus());
		}
		sql.append(" order by bookDate desc ");
		Object[] args = new Object[4];
		args[0]=sql.toString();
		args[1]=page.getPageIndex();
		args[2]=page.getPageSize();
		args[3]="";
		AirOrder item = null;
		dh.setBeanClass(AirOrder.class);
		List orderList = dh.queryRecords(execSql, args);
		page.setRecordCount(dh.returnOutParam(execSql, args));
		/*for (Object object : orderList) {
			item = (AirOrder) object;
			results.add(item);
		}
		String formatStr = "yyyy-MM-dd HH:mm:ss";
		for (Map map : orderList) {
			 item.setMemberNo(map.get("memberNo").toString());
			item.setOrderNo(map.get("orderNo"));
			item.setPassengers(map.get("passengers").toString());
			// System.out.println(map.get("laststate").toString());
			item.setTicketNo(map.get("ticketNo") != null ? map.get("ticketNo")
					.toString() : null);
			item.setPnrNo(map.get("pnrNo").toString());
			// 把日期放在这里
			item.setScity(map.get("scity").toString());
			item.setScityName(map.get("scityName"));
			item.setEcity(map.get("ecity"));
			item.setEcityName(map.get("ecityName"));
			item.setFlightNo(map.get("flightNo"));
			item.setBackflightNo(map.get("backflightNo") != null ? map
					.get("backflightNo") : null);
			item.setModel(map.get("model"));
			item.setBackModel(map.get("backModel") != null ? map
					.get("backModel") : null);
			item.setDepTime(new Timestamp(format.stringToDate(
					map.get("depTime").toString(), formatStr).getTime()));
			item.setBackDepTime(map.get("backDepTime") != null ? new Timestamp(
					format.stringToDate(map.get("backDepTime").toString(),
							formatStr).getTime()) : null);
			item.setArrTime(new Timestamp(format.stringToDate(
					map.get("arrTime").toString(), formatStr).getTime()));
			item.setBackArrTime(map.get("backArrTime") != null ? new Timestamp(
					format.stringToDate(map.get("backArrTime").toString(),
							formatStr).getTime()) : null);
			item.setFare(map.get("fare"));
			item.setBackFare(map.get("backFare") != null ? map.get("backFare")
					: null);
			item.setDiscount(map.get("discount"));
			item.setBackDiscount(map.get("backDiscount") != null ? map
					.get("backDiscount") : null);
			item.setCost(map.get("cost"));
			item.setBackCost(map.get("backCost") != null ? map.get("backCost")
					: null);
			item.setCabinCode(map.get("cabinCode") != null ? map
					.get("cabinCode") : null);
			item.setBackCabinCode(map.get("backCabinCode") != null ? map
					.get("backCabinCode") : null);
			item.setAircom(map.get("aircom"));
			item.setBackAircom(map.get("backAircom") != null ? map
					.get("backAircom") : null);
			item.setOrderTotal(map.get("orderTotal"));
			
			
		}*/
		dh.close();
		return orderList;
	}
	
	/**
	 * 获取可以申请行程单的订单信息
	 * @param memberNum
	 * @return
	 * @throws SQLException 
	 */
	public List<AirOrder> returnNotApplyItinOrders(String memberNum,AirOrder order,Page page) throws SQLException{
		DBHelper dh = DBHelper.getInstance();
		String execSql = "{call pro_Common_PageView(?,?,?,?)}";
		//SELECT * FROM Plane_Order WHERE memberNum=@memberNum AND orderNo NOT IN(SELECT orderNo FROM dbo.Plan_Itinerary WHERE status!=1) AND orderstatus=2 AND DATEDIFF(DAY,depTime,GETDATE())<14
		sql = new StringBuffer("SELECT * FROM Plane_Order WHERE 1=1 and memberNum='").append(memberNum).append("' AND ")
		.append("orderNo NOT IN(SELECT orderNo FROM dbo.Plan_Itinerary WHERE status!=1) AND orderstatus=2 AND DATEDIFF(DAY,depTime,GETDATE())<14");
		if(format.isNotNullOrEmpty(order.getStartDate())){
			sql.append(" AND bookDate>'").append(order.getStartDate()).append("' ");
		}
		if(format.isNotNullOrEmpty(order.getEndDate())){
			sql.append(" AND bookDate<'").append(format.getInstance().returnNextDate(order.getEndDate().toString())).append("' ");
		}
		if(format.isNotNullOrEmpty(order.getOrderStatus())){
			sql.append(" AND orderStatus=").append(order.getOrderStatus());
		}
		sql.append(" order by bookDate desc ");
		//String sql = "{call returnNotApplyItinOrders("+memberNum+")}";
		Object[] args = new Object[4];
		args[0]=sql.toString();
		args[1]=page.getPageIndex();
		args[2]=page.getPageSize();
		args[3]="";
		AirOrder item = null;
		dh.setBeanClass(AirOrder.class);
		List orderList = dh.queryRecords(execSql, args);
		page.setRecordCount(dh.returnOutParam(execSql, args));
		dh.close();
		return orderList;
	}
	
	/**
	 * 单个订单查询
	 * @param orderId
	 * @return
	 */
	public AirOrder returnOrderByOrderId(String orderId){
		DBHelper dh = DBHelper.getInstance();
		String sql = new StringBuffer("SELECT * FROM Plane_Order WHERE 1=1 AND orderNo='").append(orderId).append("'").toString();
		dh.setBeanClass(AirOrder.class);
		AirOrder order=null;
		try {
			order = (AirOrder)dh.query(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dh.close();
		return order;
	}
}
